﻿create or replace package PKG_STD_TREE is

  -- Author  : HY
  -- Created : 2012-11-09 오전 10:57:23
  -- Purpose : 표준코드 패키지 (TREE 반환, 수정, 삭제)

  -- Public type declarations
  type CUR_TREE_DATA is ref cursor;

  -- Public function and procedure declarations
  procedure SELECT_STD_TREE (START_ID IN NUMBER, OUTDATA_OUT OUT CUR_TREE_DATA);
  procedure ADD_STD_TREE (PAR_OBJECTID_IN IN NUMBER, GRP_OBJECTID_IN IN NUMBER, CODE_IN IN VARCHAR2, PATTERN_IN IN VARCHAR2, KOR_NAME_IN IN VARCHAR2, ENG_NAME_IN IN VARCHAR2, FULL_NAME_IN IN VARCHAR2, DENSITY_IN IN NUMBER, DEFINITION_IN IN VARCHAR2, KS_CODE_IN IN VARCHAR2, KS_STANDARD_CODE_IN IN VARCHAR2, JIS_CODE_IN IN VARCHAR2, JIS_STANDARD_CODE_IN IN VARCHAR2, CHARACTERISTIC_IN IN VARCHAR2, REMARK_IN IN VARCHAR2, CREATE_USER_IN IN NUMBER);
  procedure UPDATE_STD_TREE (OBJECTID_IN IN NUMBER, PAR_OBJECTID_IN IN NUMBER, GRP_OBJECTID_IN IN NUMBER, CODE_IN IN VARCHAR2, PATTERN_IN IN VARCHAR2, KOR_NAME_IN IN VARCHAR2, ENG_NAME_IN IN VARCHAR2, FULL_NAME_IN IN VARCHAR2, DENSITY_IN IN NUMBER, DEFINITION_IN IN VARCHAR2, KS_CODE_IN IN VARCHAR2, KS_STANDARD_CODE_IN IN VARCHAR2, JIS_CODE_IN IN VARCHAR2, JIS_STANDARD_CODE_IN IN VARCHAR2, CHARACTERISTIC_IN IN VARCHAR2, REMARK_IN IN VARCHAR2, STATE_IN IN NUMBER, MODIFY_USER_IN IN NUMBER);
  procedure DELETE_STD_TREE (OBJECTID_IN IN NUMBER);
end PKG_STD_TREE;







create or replace package body PKG_STD_TREE as
  procedure SELECT_STD_TREE(START_ID in number, OUTDATA_OUT out CUR_TREE_DATA) is
  begin

  open OUTDATA_OUT FOR SELECT LEVEL AS LV, A.KOR_NAME FROM STDCODE A START WITH A.PAR_OBJECTID = START_ID CONNECT BY PRIOR A.OBJECTID = A.PAR_OBJECTID;

  end SELECT_STD_TREE;

  -- 추가
  procedure ADD_STD_TREE (PAR_OBJECTID_IN IN NUMBER, GRP_OBJECTID_IN IN NUMBER, CODE_IN IN VARCHAR2, PATTERN_IN IN VARCHAR2, KOR_NAME_IN IN VARCHAR2, ENG_NAME_IN IN VARCHAR2, FULL_NAME_IN IN VARCHAR2, DENSITY_IN IN NUMBER, DEFINITION_IN IN VARCHAR2, KS_CODE_IN IN VARCHAR2, KS_STANDARD_CODE_IN IN VARCHAR2, JIS_CODE_IN IN VARCHAR2, JIS_STANDARD_CODE_IN IN VARCHAR2, CHARACTERISTIC_IN IN VARCHAR2, REMARK_IN IN VARCHAR2, CREATE_USER_IN IN NUMBER) IS
  begin
       INSERT INTO STDCODE (OBJECTID, PAR_OBJECTID, GRP_OBJECTID, CODE, PATTERN, KOR_NAME, ENG_NAME, FULL_NAME, DENSITY, DEFINITION, KS_CODE, KS_STANDARD_CODE, JIS_CODE, JIS_STANDARD_CODE, CHARACTERISTIC, REMARK , CREATE_USER)
       VALUES (SEQ_STDCODE.NEXTVAL, PAR_OBJECTID_IN, GRP_OBJECTID_IN, CODE_IN, PATTERN_IN, KOR_NAME_IN, ENG_NAME_IN, FULL_NAME_IN, DENSITY_IN, DEFINITION_IN, KS_CODE_IN, KS_STANDARD_CODE_IN, JIS_CODE_IN, JIS_STANDARD_CODE_IN, CHARACTERISTIC_IN, REMARK_IN , CREATE_USER_IN);
  end  ADD_STD_TREE;
  -- 수정
  procedure UPDATE_STD_TREE (OBJECTID_IN IN NUMBER, PAR_OBJECTID_IN IN NUMBER, GRP_OBJECTID_IN IN NUMBER, CODE_IN IN VARCHAR2, PATTERN_IN IN VARCHAR2, KOR_NAME_IN IN VARCHAR2, ENG_NAME_IN IN VARCHAR2, FULL_NAME_IN IN VARCHAR2, DENSITY_IN IN NUMBER, DEFINITION_IN IN VARCHAR2, KS_CODE_IN IN VARCHAR2, KS_STANDARD_CODE_IN IN VARCHAR2, JIS_CODE_IN IN VARCHAR2, JIS_STANDARD_CODE_IN IN VARCHAR2, CHARACTERISTIC_IN IN VARCHAR2, REMARK_IN IN VARCHAR2, STATE_IN IN NUMBER, MODIFY_USER_IN IN NUMBER) is
  begin
       UPDATE STDCODE A SET A.PAR_OBJECTID = PAR_OBJECTID_IN, A.GRP_OBJECTID = GRP_OBJECTID_IN, A.CODE = CODE_IN, A.PATTERN = PATTERN_IN,  A.KOR_NAME = KOR_NAME_IN, A.ENG_NAME = ENG_NAME_IN, A.FULL_NAME = FULL_NAME_IN, A.DENSITY = DENSITY_IN, A.DEFINITION = DEFINITION_IN, A.KS_CODE = KS_CODE_IN, A.KS_STANDARD_CODE = KS_STANDARD_CODE_IN, A.JIS_CODE = JIS_CODE_IN, A.JIS_STANDARD_CODE = JIS_STANDARD_CODE_IN, A.CHARACTERISTIC = CHARACTERISTIC_IN, A.REMARK = REMARK_IN, A.STATE = STATE_IN, A.MODIFY_DATE = SYSDATE, A.MODIFY_USER = MODIFY_USER_IN WHERE A.OBJECTID = OBJECTID_IN;
       UPDATE STDCODE A SET A.PATTERN = (SELECT B.PATTERN || A.CODE FROM STDCODE B WHERE B.OBJECTID = A.PAR_OBJECTID), A.MODIFY_DATE = SYSDATE, A.MODIFY_USER = MODIFY_USER_IN
       WHERE A.OBJECTID IN (SELECT C.OBJECTID FROM STDCODE C START WITH C.PAR_OBJECTID = OBJECTID_IN CONNECT BY PRIOR C.OBJECTID = C.PAR_OBJECTID);
  end  UPDATE_STD_TREE;

  -- 삭제
  procedure DELETE_STD_TREE (OBJECTID_IN IN NUMBER) is
  begin
       DELETE FROM STDCODE A WHERE A.OBJECTID = OBJECTID_IN;
       DELETE FROM STDCODE A WHERE A.OBJECTID IN (SELECT OBJECTID FROM STDCODE B START WITH B.PAR_OBJECTID = OBJECTID_IN CONNECT BY PRIOR B.OBJECTID = B.PAR_OBJECTID);
  end  DELETE_STD_TREE;
end PKG_STD_TREE;
